import os
if os.getenv('AG_ENV_PYTHON_PATH') is None:
    ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
else:
    ROOT_DIR = os.getcwd() + os.sep
    # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
parent_dir = os.path.dirname(ROOT_DIR)
import importlib.util
abstractjob_path = parent_dir+'/SKO/AbstractDPJob.py'
spec = importlib.util.spec_from_file_location('AbstractDPJob', abstractjob_path)
abstractjob_module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(abstractjob_module)
AbstractDPJob = abstractjob_module.AbstractDPJob


# from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os

class CheckJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None, p_price_sql=None):

        super(CheckJob, self).__init__()
        self.tmpl_no = p_tmpl_no

        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(CheckJob, self).do_execute()
        """
        通过JSON传入参数
        """
        # start = datetime.datetime.now()
        # row_data = self.body_dict
        # tmpl_no = row_data['tmpl_no']
        tmpl_no = self.tmpl_no

        # 数据库配置写死
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'g0mazzai'
        DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'

        # 数据库连接函数写死
        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                                       DB_PORT_MPP_DB2,
                                       DB_DBNAME_MPP_DB2,
                                       DB_USER_MPP_DB2,
                                       DB_PASSWORD_MPP_DB2)
        # 配置参数
        max_constant = 100000
        min_constant = 0
        # 写死JSON传入变量
        # tmpl_no = 'TM202308181451'
        # 读数据库数据
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COALKIND_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_meizhong = pd.read_excel('模型煤种配置表.xlsx')
        data_meizhong.columns = data_meizhong.columns.str.upper()
        # data_meizhong = data_meizhong[data_meizhong['TMPL_NO'] == tmpl_no]
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_PRECOND_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_qianti = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_qianti = pd.read_excel('前提条件配置表.xlsx')
        data_qianti.columns = data_qianti.columns.str.upper()
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_MAINPARM_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_zhuyao = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_zhuyao = pd.read_excel('主要参数配置表.xlsx')
        data_zhuyao.columns = data_zhuyao.columns.str.upper()
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_bilikegong = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_bilikegong = pd.read_excel('煤比例可供资源配置表.xlsx')
        data_bilikegong.columns = data_bilikegong.columns.str.upper()
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_IND_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_meizhi = pd.read_excel('煤质信息配置表.xlsx')
        data_meizhi.columns = data_meizhi.columns.str.upper()
        sql = " select * " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_VARINDLIMIT_INFO " \
              " where TMPL_NO ='%s' " % (tmpl_no)
        data_shangxiaxian = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_shangxiaxian = pd.read_excel('煤质上下限配置表.xlsx')
        data_shangxiaxian.columns = data_shangxiaxian.columns.str.upper()
        # sql = price_sql
        # data_price = pd.read_sql_query(sql, con=db_conn_mpp)
        # data_price = pd.read_excel('手工价格配置表.xlsx')
        # data_price.columns = data_price.columns.str.upper()
        # 数据处理，不要外购焦粉
        # data_meizhong = data_meizhong[(data_meizhong['BIG_VAR'] != '烧结煤') | (data_meizhong['VAR'] != '焦粉')]
        data_meizhong = data_meizhong.reset_index(drop=True)
        data0 = data_meizhong.copy()
        data0.drop(['REC_ID'], axis=1, inplace=True)
        data0.drop(['TMPL_NO'], axis=1, inplace=True)
        data0.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
        data0.drop(['REC_CREATOR'], axis=1, inplace=True)
        data0.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
        data0.drop(['REC_REVISOR'], axis=1, inplace=True)
        row_count = data0.shape[0]

        def __cal_rank_pinzhong(x):
            rst = 0
            if x.BIG_VAR == '喷吹煤':
                rst = 1
            elif x.BIG_VAR == '发电煤':
                rst = 2
            elif x.BIG_VAR == '烧结煤':
                rst = 3
            return rst

        data0['pinzhong'] = data0.apply(lambda x: __cal_rank_pinzhong(x), axis=1)

        def __cal_rank_xingzhuang(x):
            rst = 0
            if x.BIG_VAR == '喷吹煤':
                if x.VAR == '烟煤':
                    rst = 1
                elif x.VAR == '无烟煤':
                    rst = 2
                elif x.VAR == '兰炭':
                    rst = 3
            elif x.BIG_VAR == '发电煤':
                if x.VAR == '大同类':
                    rst = 1
                elif x.VAR == '神府类':
                    rst = 2
                elif x.VAR == '兰炭类':
                    rst = 3
            elif x.BIG_VAR == '烧结煤':
                if x.VAR == '无烟煤':
                    rst = 1
                elif x.VAR == '兰炭':
                    rst = 2
                elif x.VAR == '焦粉':
                    rst = 3
            return rst

        data0['xingzhuang'] = data0.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)

        def __cal_rank_group(x):
            rst = str(x.pinzhong) + '_' + str(x.xingzhuang)
            return rst

        data0['group'] = data0.apply(lambda x: __cal_rank_group(x), axis=1)
        data0 = data0.reset_index(drop=False)
        data0.rename(columns={'index': 'index_old'}, inplace=True)

        data0['rank0'] = data0['index_old'].groupby(data0['group']).rank()
        data0['pinming'] = data0['rank0'].astype(int)

        def __cal_rank_mark(x):
            rst = str(x.group) + '_' + str(x.pinming)
            return rst

        data0['mark'] = data0.apply(lambda x: __cal_rank_mark(x), axis=1)
        # data0.drop(['group'], axis=1, inplace=True)
        data0.drop(['rank0'], axis=1, inplace=True)
        # 生成mark列拼接动态参数
        # 统计个数，做个var_df以便后续对应
        big_var_list = ['喷吹煤', '发电煤', '烧结煤']
        var_list1 = ['烟煤', '无烟煤', '兰炭']
        var_list2 = ['大同类', '神府类', '兰炭类']
        var_list3 = ['无烟煤', '兰炭', '焦粉']
        var_df = pd.DataFrame(columns=['BIG_VAR', 'VAR', 'group', 'count'])
        dict = {}
        # var_list = []
        ldict = {}
        for i in range(1, len(big_var_list) + 1):
            # print(i)
            # exec('var_list =var_list{}'.format(i))
            exec('var_list =var_list{}'.format(i), locals(), ldict)
            var_list = ldict["var_list"]
            # print(var_list)
            for j in range(1, len(var_list) + 1):
                # print(j)
                df = data_meizhong[
                    (data_meizhong['BIG_VAR'] == big_var_list[i - 1]) & (data_meizhong['VAR'] == var_list[j - 1])]
                row_count = df.shape[0]
                # print("行数：", row_count)
                dict['BIG_VAR'] = big_var_list[i - 1]
                dict['VAR'] = var_list[j - 1]
                dict['group'] = str(i) + '_' + str(j)
                dict['count'] = row_count
                new_row = pd.Series(dict)
                var_df = var_df.append(new_row, ignore_index=True)
        # print('finish')
        # #动态变量测试
        ###价格
        # data_price.rename(columns={'UNIT_PRICE': 'PRICE'}, inplace=True)
        # data_price = data_price[['PROD_DSCR','PRICE']]
        # v = ['PROD_DSCR']
        # df3 = pd.merge(data0, data_price, on=v, how='left')
        # df3.PRICE.fillna(max_constant,inplace = True)
        # for index, row in df3.iterrows():
        #     exec('price_{} ={}'.format(row['mark'],row['PRICE']))
        #     exec('print(price_{})'.format(row['mark']))
        ###可控资源
        data_bilikegong.rename(columns={'INV_WT': 'WT'}, inplace=True)
        data_bilikegong_1 = data_bilikegong[data_bilikegong['FLAG'] == '品名']
        data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
        data_bilikegong_1 = data_bilikegong_1[['PROD_DSCR', 'WT']]
        v = ['PROD_DSCR']
        df3 = pd.merge(data0, data_bilikegong_1, on=v, how='left')
        # 可供资源为空，无穷大库存
        df3.WT.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('kegongziyuan_{} ={}'.format(row['mark'], row['WT']))
        ###比例上下限
        data_bilikegong.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
        data_bilikegong.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
        data_bilikegong_1 = data_bilikegong[data_bilikegong['FLAG'] == '品名']
        data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
        data_bilikegong_1 = data_bilikegong_1[['PROD_DSCR', 'UL', 'LL']]
        v = ['PROD_DSCR']
        df3 = pd.merge(data0, data_bilikegong_1, on=v, how='left')
        # 比例0-100
        df3.UL.fillna(100, inplace=True)
        df3.LL.fillna(0, inplace=True)

        for index, row in df3.iterrows():
            exec('ul_{} ={}'.format(row['mark'], row['UL']))
            exec('ll_{} ={}'.format(row['mark'], row['LL']))
        data_bilikegong_2 = data_bilikegong[data_bilikegong['FLAG'] == '性状']
        data_bilikegong_2 = data_bilikegong_2.reset_index(drop=True)
        def __cal_new_var(x):
            rst = str(x.BIG_VAR) + '_' + str(x.VAR)
            return rst
        data_bilikegong_2['NEW_VAR'] = data_bilikegong_2.apply(lambda x: __cal_new_var(x), axis=1)
        data_bilikegong_2 = data_bilikegong_2[['NEW_VAR', 'UL', 'LL']]
        var_df1 = var_df.copy()
        def __cal_new_var(x):
            rst = str(x.BIG_VAR) + '_' + str(x.VAR)
            return rst
        var_df1['NEW_VAR'] = var_df1.apply(lambda x: __cal_new_var(x), axis=1)
        v = ['NEW_VAR']
        # print(var_df1)
        # print(data_bilikegong_2)
        df3 = pd.merge(var_df1, data_bilikegong_2, on=v, how='left')
        # print(df3)
        # data_bilikegong_1 = data_bilikegong[data_bilikegong['FLAG'] == '性状']
        # data_bilikegong_1 = data_bilikegong_1.reset_index(drop=True)
        # data_bilikegong_1 = data_bilikegong_1[['VAR', 'UL', 'LL']]
        # v = ['VAR']
        # df3 = pd.merge(var_df, data_bilikegong_1, on=v, how='left')
        # 比例0-100
        df3.UL.fillna(100, inplace=True)
        df3.LL.fillna(0, inplace=True)
        for index, row in df3.iterrows():
            exec('ul_{} ={}'.format(row['group'], row['UL']))
            exec('ll_{} ={}'.format(row['group'], row['LL']))
        ###煤质
        data_meizhi.rename(columns={'H2O': 'shui'}, inplace=True)
        data_meizhi.rename(columns={'ASH': 'hui'}, inplace=True)
        data_meizhi.rename(columns={'COKE_VM': 'huifa'}, inplace=True)
        data_meizhi.rename(columns={'S': 'liu'}, inplace=True)
        data_meizhi.rename(columns={'COKE_FIXCARBON': 'gu'}, inplace=True)
        data_meizhi.rename(columns={'WEAR_ERROR_FLAG': 'mo'}, inplace=True)
        data_meizhi.rename(columns={'COKE_HOTVALUE': 're'}, inplace=True)
        data_meizhi.rename(columns={'C': 'cc'}, inplace=True)

        data_meizhi_1 = data_meizhi[['PROD_DSCR', 'shui', 'hui', 'huifa', 'liu', 'gu', 'mo', 're', 'cc']]
        v = ['PROD_DSCR']
        df3 = pd.merge(data0, data_meizhi_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('shui_{} ={}'.format(row['mark'], row['shui']))
            exec('hui_{} ={}'.format(row['mark'], row['hui']))
            exec('huifa_{} ={}'.format(row['mark'], row['huifa']))
            exec('liu_{} ={}'.format(row['mark'], row['liu']))
            exec('gu_{} ={}'.format(row['mark'], row['gu']))
            exec('mo_{} ={}'.format(row['mark'], row['mo']))
            exec('re_{} ={}'.format(row['mark'], row['re']))
            exec('cc_{} ={}'.format(row['mark'], row['cc']))
        ###煤质上下限
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '喷吹煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)
        penchui_shui_ul = data_shangxiaxian_1.loc[0]['H2O']
        penchui_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        penchui_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        penchui_liu_ul = data_shangxiaxian_1.loc[0]['S']
        penchui_gu_ul = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        penchui_mo_ul = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        penchui_re_ul = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        penchui_cc_ul = data_shangxiaxian_1.loc[0]['C']
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '喷吹煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(min_constant, inplace=True)

        penchui_shui_ll = data_shangxiaxian_1.loc[0]['H2O']
        penchui_hui_ll = data_shangxiaxian_1.loc[0]['ASH']
        penchui_huifa_ll = data_shangxiaxian_1.loc[0]['COKE_VM']
        penchui_liu_ll = data_shangxiaxian_1.loc[0]['S']
        penchui_gu_ll = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        penchui_mo_ll = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        penchui_re_ll = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        penchui_cc_ll = data_shangxiaxian_1.loc[0]['C']
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '发电煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)

        fadian_shui_ul = data_shangxiaxian_1.loc[0]['H2O']
        fadian_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        fadian_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        fadian_liu_ul = data_shangxiaxian_1.loc[0]['S']
        fadian_gu_ul = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        fadian_mo_ul = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        fadian_re_ul = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        fadian_cc_ul = data_shangxiaxian_1.loc[0]['C']
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '发电煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(min_constant, inplace=True)

        fadian_shui_ll = data_shangxiaxian_1.loc[0]['H2O']
        fadian_hui_ll = data_shangxiaxian_1.loc[0]['ASH']
        fadian_huifa_ll = data_shangxiaxian_1.loc[0]['COKE_VM']
        fadian_liu_ll = data_shangxiaxian_1.loc[0]['S']
        fadian_gu_ll = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        fadian_mo_ll = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        fadian_re_ll = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        fadian_cc_ll = data_shangxiaxian_1.loc[0]['C']
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '烧结煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)

        shaojie_shui_ul = data_shangxiaxian_1.loc[0]['H2O']
        shaojie_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        shaojie_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        shaojie_liu_ul = data_shangxiaxian_1.loc[0]['S']
        shaojie_gu_ul = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        shaojie_mo_ul = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        shaojie_re_ul = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        shaojie_cc_ul = data_shangxiaxian_1.loc[0]['C']
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '烧结煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(min_constant, inplace=True)

        shaojie_shui_ll = data_shangxiaxian_1.loc[0]['H2O']
        shaojie_hui_ll = data_shangxiaxian_1.loc[0]['ASH']
        shaojie_huifa_ll = data_shangxiaxian_1.loc[0]['COKE_VM']
        shaojie_liu_ll = data_shangxiaxian_1.loc[0]['S']
        shaojie_gu_ll = data_shangxiaxian_1.loc[0]['COKE_FIXCARBON']
        shaojie_mo_ll = data_shangxiaxian_1.loc[0]['WEAR_ERROR_FLAG']
        shaojie_re_ll = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        shaojie_cc_ll = data_shangxiaxian_1.loc[0]['C']

        # 年度前提条件表
        kongmeizhibiao = data_qianti.loc[0]['TOTAL_COAL_AMT']
        tieshuichanliang = data_qianti.loc[0]['MOLTIRON_WT']
        jiaotanchanliang = data_qianti.loc[0]['COKE_OUTPUT']
        fadianliang = data_qianti.loc[0]['ELECOUT']
        shaojiechanliang = data_qianti.loc[0]['SINTER_OUTPUT']
        zongmeibi = data_qianti.loc[0]['BF_PCR']
        CDQbi = data_qianti.loc[0]['CDQ_RATE']

        # 纯煤比 = 总煤比 - CDQ比
        chunmeibi = data_qianti.loc[0]['BF_PCR1']
        zongjiaobi = data_qianti.loc[0]['BF_CR']
        shaojieranliaobi = data_qianti.loc[0]['SINTER_FUEL_RATIO']
        # 主要参数表
        chengjiaolv = data_zhuyao.loc[0]['COKING_COKEYR']
        yejinjiaolv = data_zhuyao.loc[0]['COKING_METCOKRAT']
        cujiaolv = data_zhuyao.loc[0]['SINTER_CRSCOKE_RATIO']
        meidianbi = data_zhuyao.loc[0]['COAL_ELEC_RATIO']
        fadianmeihao = data_zhuyao.loc[0]['COAL_USE']
        lianjiaomeishuifen = data_zhuyao.loc[0]['COKING_COALBLD_MOIST']
        penchuimeishuifen = data_zhuyao.loc[0]['BF_COALINJECT_MOIST']
        shaojiemeishuifen = data_zhuyao.loc[0]['SINTER_COAL_MOIST']
        meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE']
        waigoujiaoshuifen = data_zhuyao.loc[0]['PCOKE_MOIST']
        waigoujiaofenlv = data_zhuyao.loc[0]['PCOKE_COKEPOWD_RATE']
        shaojiejiaofenshuifen = data_zhuyao.loc[0]['SINTER_COKE_MOIST']
        lantanshuifen = data_zhuyao.loc[0]['SEMICOKE_MOIST']
        jiaotanchaochanlv = data_zhuyao.loc[0]['COKING_OVERPROD_RATE']

        # print('参数读取完毕！！')
        ############################
        # 参数读取完毕
        # 炼焦煤
        CAL_WT_lianjiaomei = jiaotanchanliang / chengjiaolv * 100 / (100 - lianjiaomeishuifen) * 100 * (
                    100 + meitanwushunlv) / 100
        WT_lianjiaomei = CAL_WT_lianjiaomei * (100 + jiaotanchaochanlv) / 100
        # 外购焦炭    （补炼焦煤）
        WT_waigoujiaotan = (tieshuichanliang * zongjiaobi / 1000 - jiaotanchanliang * yejinjiaolv / 100) / (
                    100 - waigoujiaofenlv) * 100 / (100 - waigoujiaoshuifen) * 100

        # 构建配置系数df
        coef_df = pd.DataFrame(columns=['mark'])
        dict = {}
        data0_1 = data0[(data0['BIG_VAR'] == '喷吹煤') & (data0['VAR'] == '烟煤')]
        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '喷吹煤') & (data0['VAR'] == '无烟煤')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '发电煤') & (data0['VAR'] == '大同类')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '发电煤') & (data0['VAR'] == '神府类')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '烧结煤') & (data0['VAR'] == '无烟煤')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '喷吹煤') & (data0['VAR'] == '兰炭')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '发电煤') & (data0['VAR'] == '兰炭类')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        data0_1 = data0[(data0['BIG_VAR'] == '烧结煤') & (data0['VAR'] == '兰炭') & (data0['VAR'] == '焦粉')]

        data0_1 = data0_1.reset_index(drop=True)
        for index, row in data0_1.iterrows():
            exec("dict['mark'] = '{}'".format(row['mark']))
            new_row = pd.Series(dict)
            coef_df = coef_df.append(new_row, ignore_index=True)
        mark_list = coef_df['mark'].to_list()
        # len1不包括兰炭的煤种个数
        data0_1 = data0[(data0['VAR'] != '兰炭类') & (data0['VAR'] != '兰炭') & (data0['VAR'] != '焦粉')]
        data0_1 = data0_1.reset_index(drop=True)
        len1 = len(data0_1)

        a11 = (100 - penchuimeishuifen) / 100 / (100 + meitanwushunlv) * 100
        a12 = (100 - lantanshuifen) / 100
        a21 = 1
        a22 = 1
        a31 = (100 - shaojiemeishuifen) / 100 / (100 + meitanwushunlv) * 100
        a32 = (100 - shaojiejiaofenshuifen) / 100
        b0 = kongmeizhibiao - WT_lianjiaomei
        b1 = tieshuichanliang * chunmeibi / 1000
        b2 = fadianliang * meidianbi / 100 * fadianmeihao / 100 * (100 + meitanwushunlv) / 100
        b3 = shaojiechanliang * shaojieranliaobi / 1000 - jiaotanchanliang * cujiaolv / 100 - WT_waigoujiaotan * waigoujiaofenlv / 100 * (
                100 - waigoujiaoshuifen) / 100
        # 构建所有参数的df
        canshu_df = pd.DataFrame(columns=['编号', '参数名', '步骤', 'mark', 'group', 'big_var', 'meizhi'])
        dict_canshu = {}
        j_start = 0
        # 必要条件，控煤指标
        array_len = len(mark_list)
        import numpy as np
        from numpy import array
        y0 = array([[0] * array_len], dtype=float)
        for i in range(0, int(len1)):
            y0[0, i] = 1
        m0 = array([b0])
        dict_canshu['编号'] = 0
        dict_canshu['参数名'] = '控煤指标'
        dict_canshu['步骤'] = '前提条件'
        new_row = pd.Series(dict_canshu)
        canshu_df = canshu_df.append(new_row, ignore_index=True)
        dict_canshu = {}
        j_start = j_start + 1
        # 可供资源
        for j in range(j_start, j_start + int(array_len)):
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            exec('y{}[0,j-j_start] = 1'.format(j))
            mark_tmp = mark_list[j - 1]
            exec('m{} = array([kegongziyuan_{}])'.format(j, mark_tmp))
            dict_canshu['编号'] = j
            dict_canshu['参数名'] = '可供资源'
            if mark_tmp[0] == '1':
                buzhou_tmp = '喷吹煤'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '发电煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '烧结煤'
            dict_canshu['步骤'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
        j_start = j_start + int(array_len)

        # 性状比例
        var_list = var_df['group'].to_list()
        ldict3 = {}
        ldict4 = {}
        for var_tmp in var_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_{}'.format(var_tmp), locals(), ldict3)
            true_value = ldict3["true_value"]
            # print(true_value)
            exec('false_value = - ul_{}'.format(var_tmp), locals(), ldict3)
            false_value = ldict3["false_value"]
            # print(false_value)
            # exec('true_value = 100 - ul_{}'.format(var_tmp))
            # exec('false_value = - ul_{}'.format(var_tmp))
            var_tmp_top = var_tmp[0]

            def __cal_coef(x):
                if x.mark[0:3] == var_tmp:
                    rst = true_value
                elif x.mark[0:3] != var_tmp and x.mark[0] == var_tmp_top:
                    rst = false_value
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '性状比例上限'
            if var_tmp[0] == '1':
                buzhou_tmp = '喷吹煤'
            elif var_tmp[0] == '2':
                buzhou_tmp = '发电煤'
            elif var_tmp[0] == '3':
                buzhou_tmp = '烧结煤'
            dict_canshu['步骤'] = buzhou_tmp
            dict_canshu['group'] = var_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}

            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_{} - 100'.format(var_tmp), locals(), ldict4)
            true_value = ldict4["true_value"]
            # print(true_value)
            exec('false_value = ll_{}'.format(var_tmp), locals(), ldict4)
            false_value = ldict4["false_value"]
            # print(false_value)
            # exec('true_value = ll_{} - 100'.format(var_tmp))
            # exec('false_value = ll_{}'.format(var_tmp))
            var_tmp_top = var_tmp[0]

            def __cal_coef(x):
                if x.mark[0:3] == var_tmp:
                    rst = true_value
                elif x.mark[0:3] != var_tmp and x.mark[0] == var_tmp_top:
                    rst = false_value
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '性状比例下限'
            if var_tmp[0] == '1':
                buzhou_tmp = '喷吹煤'
            elif var_tmp[0] == '2':
                buzhou_tmp = '发电煤'
            elif var_tmp[0] == '3':
                buzhou_tmp = '烧结煤'
            dict_canshu['步骤'] = buzhou_tmp
            dict_canshu['group'] = var_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 品种比例
        # true_value = 0
        # false_value = 0
        ldict1 = {}
        ldict2 = {}
        for mark_tmp in mark_list:
            # 上限
            coef_df1 = coef_df.copy()
            mark_tmp_top = mark_tmp[0]
            exec('true_value = 100 - ul_{}'.format(mark_tmp), locals(), ldict1)
            true_value = ldict1["true_value"]
            # print(true_value)
            exec('false_value = - ul_{}'.format(mark_tmp), locals(), ldict1)
            false_value = ldict1["false_value"]
            # print(false_value)

            # exec('true_value = 100 - ul_{}'.format(mark_tmp))
            # exec('false_value = - ul_{}'.format(mark_tmp))

            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                elif x.mark != mark_tmp and x.mark[0] == mark_tmp_top:
                    rst = false_value
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '品种比例上限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '喷吹煤'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '发电煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '烧结煤'
            dict_canshu['步骤'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            mark_tmp_top = mark_tmp[0]
            exec('true_value = ll_{} - 100'.format(mark_tmp), locals(), ldict2)
            true_value = ldict2["true_value"]
            # print(true_value)
            exec('false_value = ll_{}'.format(mark_tmp), locals(), ldict2)
            false_value = ldict2["false_value"]
            # print(false_value)

            # exec('true_value = ll_{} - 100'.format(mark_tmp))
            # exec('false_value = ll_{}'.format(mark_tmp))

            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                elif x.mark != mark_tmp and x.mark[0] == mark_tmp_top:
                    rst = false_value
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '品种比例下限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '喷吹煤'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '发电煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '烧结煤'
            dict_canshu['步骤'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 煤质上下限
        meizhi_list = ['shui', 'hui', 'huifa', 'liu', 'gu', 'mo', 're', 'cc']
        meizhi_list_fadian = ['shui', 'hui', 'huifa', 'liu', 'gu', 're', 'cc']
        # 喷吹煤
        big_var_tmp = 'penchui'
        for meizhi_tmp in meizhi_list:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '1':
                    exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                                   meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '上限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '喷吹煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '1':
                    exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                                   mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '下限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '喷吹煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 发电煤
        big_var_tmp = 'fadian'
        for meizhi_tmp in meizhi_list_fadian:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '2':
                    exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                                   meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '上限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '发电煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '2':
                    exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                                   mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '下限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '发电煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 烧结煤
        big_var_tmp = 'shaojie'
        for meizhi_tmp in meizhi_list:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '3':
                    exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                                   meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '上限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '烧结煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                mark_tmp_top = mark_tmp[0]
                if mark_tmp_top == '3':
                    exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                                   mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['编号'] = j_start
            dict_canshu['参数名'] = '下限'
            dict_canshu['步骤'] = '煤质上下限'
            dict_canshu['big_var'] = '烧结煤'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # print('y生成完毕')
        # 拼接
        y_str = 'y0'
        # yyy = (y0,y0)
        for j in range(1, j_start):
            y_str = y_str + ',' + 'y' + str(j)
        # exec('yyy =({})'.format(y_str))
        exec('yyy =({})'.format(y_str), locals(), ldict)
        yyy = ldict["yyy"]
        Y = np.concatenate(yyy, axis=0)

        m_str = 'm0'
        # mmm = (m0,m0)
        for j in range(1, j_start):
            m_str = m_str + ',' + 'm' + str(j)
        # exec('mmm =({})'.format(m_str))
        exec('mmm =({})'.format(m_str), locals(), ldict)
        mmm = ldict["mmm"]
        M = np.concatenate(mmm, axis=0)
        # print('finish')
        # c_str = ''
        # for mark_tmp in mark_list:
        #     if mark_tmp == '1_1_1':
        #         c_str = c_str + 'price_1_1_1'
        #     else:
        #         exec("c_str = c_str + ',' + 'price_{}'".format(mark_tmp))
        # exec("c = array([{}])".format(c_str))
        c = array([[1] * array_len], dtype=float)

        def judge_solution_exist(e1, f1):
            """
            传入e1，f1进行线性规划求解，判断是否有可行域
            """
            coef_df1 = coef_df.copy()

            def __cal_coef(x):
                if x.mark[0:3] != '1_3' and x.mark[0] == '1':
                    rst = a31
                elif x.mark[0:3] == '1_3':
                    rst = a32
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            e21 = array([[0] * array_len], dtype=float)
            for index, row in coef_df1.iterrows():
                e21[0, index] = row['coef']
            coef_df1 = coef_df.copy()

            def __cal_coef(x):
                if x.mark[0:3] != '2_3' and x.mark[0] == '2':
                    rst = a31
                elif x.mark[0:3] == '2_3':
                    rst = a32
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            e22 = array([[0] * array_len], dtype=float)
            for index, row in coef_df1.iterrows():
                e22[0, index] = row['coef']
            coef_df1 = coef_df.copy()

            def __cal_coef(x):
                if x.mark[0:3] != '3_2' and x.mark[0:3] != '3_3' and x.mark[0] == '3':
                    rst = a31
                elif x.mark[0:3] == '3_2':
                    rst = a32
                else:
                    rst = 0
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            e23 = array([[0] * array_len], dtype=float)
            for index, row in coef_df1.iterrows():
                e23[0, index] = row['coef']
            e222 = (e21, e22, e23)
            e2 = np.concatenate(e222, axis=0)
            f2 = array([b1, b2, b3])
            x = cp.Variable(array_len)
            obj = cp.Minimize(c @ x)
            cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
            prob = cp.Problem(obj, cons)
            prob.solve(solver='GLPK_MI', verbose=True)
            # print("最优初始值为:", prob.value)
            # print("最优初始解为：\n", x.value)
            success = 0
            if x.value is None:
                success = 0
            else:
                success = 1
            # print(success)
            return success

        success = judge_solution_exist(e1=Y, f1=M)
        yy = y0
        mm = m0
        listb = []
        if success == 1:
            message = '约束条件合理，存在最优解'
            result_list = []
            # print(message)
        else:
            message = '约束条件不合理，不存在最优解'
            # print(message)
            # print('无解，开始循环找需要修改的约束条件')
            for i in range(1, j_start):
                yi = Y[[i]]
                mi = M[[i]]
                Yy = np.concatenate((yy, yi), axis=0)
                Mm = np.concatenate((mm, mi), axis=0)
                success = judge_solution_exist(e1=Yy, f1=Mm)
                if success == 1:
                    yy = Yy
                    mm = Mm
                else:
                    yy = yy
                    mm = mm
                    # print(i)
                    listb.append(i)
                    # print(listb)
            df2 = canshu_df.iloc[listb]
            # print('需要修改的约束条件参数为')
            # print(df2)
            df2 = df2.reset_index(drop=False)
            data0_tmp = data0[['VAR', 'PROD_DSCR', 'mark']]
            v = ['mark']
            df2 = pd.merge(df2, data0_tmp, on=v, how='left')
            var_df_tmp = var_df[['group', 'VAR']]
            var_df_tmp.rename(columns={'VAR': 'VAR2'}, inplace=True)
            v = ['group']
            df2 = pd.merge(df2, var_df_tmp, on=v, how='left')
            def __cal_coef(x):
                if x.步骤 == '煤质上下限':
                    ind=''
                    if x.meizhi == 'shui':
                        ind = '水分'
                    elif x.meizhi == 'hui':
                        ind = '灰分'
                    elif x.meizhi == 'huifa':
                        ind = '挥发分'
                    elif x.meizhi == 'liu':
                        ind = '硫分'
                    elif x.meizhi == 'gu':
                        ind = '固定碳'
                    elif x.meizhi == 'mo':
                        ind = '可磨性'
                    elif x.meizhi == 're':
                        ind = '热值'
                    elif x.meizhi == 'cc':
                        ind = 'C'
                    rst = str(x.big_var) + '_' + ind + '_' + str(x.参数名)
                elif x.参数名 == '品种比例上限' or x.参数名 == '品种比例下限':
                    rst = str(x.步骤) + '_' + str(x.PROD_DSCR) + '_' + str(x.参数名)
                elif x.参数名 == '性状比例上限' or x.参数名 == '性状比例下限':
                    rst = str(x.步骤) + '_' + str(x.VAR2) + '_' + str(x.参数名)
                else:
                    rst = x.参数名
                return rst

            df2['new_coef'] = df2.apply(lambda x: __cal_coef(x), axis=1)

            def __cal_coef_col_name(x):
                if x.步骤 == '煤质上下限':
                    if x.meizhi == 'shui':
                        rst = 'H2O'
                    elif x.meizhi == 'hui':
                        rst = 'ASH'
                    elif x.meizhi == 'huifa':
                        rst = 'COKE_VM'
                    elif x.meizhi == 'liu':
                        rst = 'S'
                    elif x.meizhi == 'gu':
                        rst = 'COKE_FIXCARBON'
                    elif x.meizhi == 'mo':
                        rst = 'WEAR_ERROR_FLAG'
                    elif x.meizhi == 're':
                        rst = 'COKE_HOTVALUE'
                    elif x.meizhi == 'cc':
                        rst = 'C'
                elif x.参数名 == '品种比例上限':
                    rst = 'MAX_VALUE'
                elif x.参数名 == '品种比例下限':
                    rst = 'MIN_VALUE'
                elif x.参数名 == '性状比例上限':
                    rst = 'MAX_VALUE'
                elif x.参数名 == '性状比例下限':
                    rst = 'MIN_VALUE'
                return rst

            df2['new_coef_col_name'] = df2.apply(lambda x: __cal_coef_col_name(x), axis=1)

            def __cal_coef_index_name(x):
                if x.参数名 == '上限':
                    rst = '上限'
                elif x.参数名 == '下限':
                    rst = '下限'
                elif x.参数名 == '品种比例上限' or x.参数名 == '品种比例下限':
                    rst = x.PROD_DSCR
                elif x.参数名 == '性状比例上限' or x.参数名 == '性状比例下限':
                    rst = x.VAR
                return rst

            df2['new_coef_index_name'] = df2.apply(lambda x: __cal_coef_index_name(x), axis=1)
            sql1 = " select a.BIG_VAR,a.VAR,a.PROD_DSCR,a.PROD_CODE,  " \
                   " b.MIN_VALUE,b.MAX_VALUE,b.INV_WT ,  " \
                   " c.H2O,c.ASH,c.COKE_VM,c.S,c.COKE_FIXCARBON,c.WEAR_ERROR_FLAG,c.COKE_HOTVALUE,c.C " \
                   " from " \
                   " (select REC_ID,BIG_VAR,VAR,PROD_DSCR,PROD_CODE " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_COALKIND_INFO " \
                   " where TMPL_NO ='%s' and BIG_VAR='喷吹煤' " \
                   " )as a " \
                   " left join " \
                   " (select PROD_DSCR,MAX_VALUE,MIN_VALUE,INV_WT " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " and flag = '品名' " \
                   " )as b " \
                   " on a.PROD_DSCR=b.PROD_DSCR " \
                   " left join " \
                   " (select PROD_DSCR,H2O,ASH,COKE_VM,S,COKE_FIXCARBON,WEAR_ERROR_FLAG,COKE_HOTVALUE,C " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_IND_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " )as c " \
                   " on a.PROD_DSCR=c.PROD_DSCR " \
                   " order by CASE " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '烟煤' THEN 1 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '无烟煤' THEN 2 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '兰炭' THEN 3 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '大同类' THEN 4 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '神府类' THEN 5 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '兰炭类' THEN 6 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '无烟煤' THEN 7 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '兰炭' THEN 8 " \
                   " ELSE 9 " \
                   " END,REC_ID " % (tmpl_no)
            sql2 = " select a.BIG_VAR,a.VAR,a.PROD_DSCR,a.PROD_CODE,  " \
                   " b.MIN_VALUE,b.MAX_VALUE,b.INV_WT ,  " \
                   " c.H2O,c.ASH,c.COKE_VM,c.S,c.COKE_FIXCARBON,c.COKE_HOTVALUE,c.C " \
                   " from " \
                   " (select REC_ID,BIG_VAR,VAR,PROD_DSCR,PROD_CODE " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_COALKIND_INFO " \
                   " where TMPL_NO ='%s' and BIG_VAR='发电煤' " \
                   " )as a " \
                   " left join " \
                   " (select PROD_DSCR,MAX_VALUE,MIN_VALUE,INV_WT " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " and flag = '品名' " \
                   " )as b " \
                   " on a.PROD_DSCR=b.PROD_DSCR " \
                   " left join " \
                   " (select PROD_DSCR,H2O,ASH,COKE_VM,S,COKE_FIXCARBON,WEAR_ERROR_FLAG,COKE_HOTVALUE,C " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_IND_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " )as c " \
                   " on a.PROD_DSCR=c.PROD_DSCR " \
                   " order by CASE " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '烟煤' THEN 1 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '无烟煤' THEN 2 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '兰炭' THEN 3 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '大同类' THEN 4 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '神府类' THEN 5 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '兰炭类' THEN 6 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '无烟煤' THEN 7 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '兰炭' THEN 8 " \
                   " ELSE 9 " \
                   " END,REC_ID " % (tmpl_no)
            sql3 = " select a.BIG_VAR,a.VAR,a.PROD_DSCR,a.PROD_CODE,  " \
                   " b.MIN_VALUE,b.MAX_VALUE,b.INV_WT ,  " \
                   " c.H2O,c.ASH,c.COKE_VM,c.S,c.COKE_FIXCARBON,c.WEAR_ERROR_FLAG,c.COKE_HOTVALUE,c.C " \
                   " from " \
                   " (select REC_ID,BIG_VAR,VAR,PROD_DSCR,PROD_CODE " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_COALKIND_INFO " \
                   " where TMPL_NO ='%s' and BIG_VAR='烧结煤' " \
                   " )as a " \
                   " left join " \
                   " (select PROD_DSCR,MAX_VALUE,MIN_VALUE,INV_WT " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " and flag = '品名' " \
                   " )as b " \
                   " on a.PROD_DSCR=b.PROD_DSCR " \
                   " left join " \
                   " (select PROD_DSCR,H2O,ASH,COKE_VM,S,COKE_FIXCARBON,WEAR_ERROR_FLAG,COKE_HOTVALUE,C " \
                   " from BG00MAZZAI.T_ADS_WH_YLMX_IND_INFO " \
                   " where TMPL_NO = (SELECT TMPL_NO from BG00MAZZAI.T_ADS_WH_YLMX_TMPL_INFO order by rec_create_time desc FETCH FIRST 1 ROWS ONLY) " \
                   " )as c " \
                   " on a.PROD_DSCR=c.PROD_DSCR " \
                   " order by CASE " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '烟煤' THEN 1 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '无烟煤' THEN 2 " \
                   " WHEN BIG_VAR ='喷吹煤' and VAR = '兰炭' THEN 3 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '大同类' THEN 4 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '神府类' THEN 5 " \
                   " WHEN BIG_VAR ='发电煤' and VAR = '兰炭类' THEN 6 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '无烟煤' THEN 7 " \
                   " WHEN BIG_VAR ='烧结煤' and VAR = '兰炭' THEN 8 " \
                   " ELSE 9 " \
                   " END,REC_ID " % (tmpl_no)
            df_out = pd.DataFrame(columns=['COEF_NAME', 'STEP_NAME', 'TABLE_NAME', 'INDEX_NUM', 'COL_NUM'])
            dict = {}
            message = message + '需要修改的约束条件参数为：'
            for index, row in df2.iterrows():
                dict['COEF_NAME'] = row['new_coef']
                message = message + row['new_coef'] + '；'
                dict['STEP_NAME'] = row['步骤']
                if row['参数名'] == '性状比例上限' or row['参数名'] == '性状比例下限':
                    dict['TABLE_NAME'] = '表1'
                elif row['参数名'] == '品种比例上限' or row['参数名'] == '品种比例下限':
                    dict['TABLE_NAME'] = '表2'
                else:
                    dict['TABLE_NAME'] = ''
                if row['步骤'] == '煤质上下限':
                    sql = " select BIG_VAR,FLAG,H2O,ASH,COKE_VM,S,COKE_FIXCARBON,WEAR_ERROR_FLAG,COKE_HOTVALUE,C " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_VARINDLIMIT_INFO " \
                          " where TMPL_NO = '%s'  " \
                          " order by CASE  " \
                          " WHEN BIG_VAR ='喷吹煤' and FLAG = '下限' THEN 1 " \
                          " WHEN BIG_VAR ='喷吹煤' and FLAG = '上限' THEN 2 " \
                          " WHEN BIG_VAR ='发电煤' and FLAG = '下限' THEN 3 " \
                          " WHEN BIG_VAR ='发电煤' and FLAG = '上限' THEN 4 " \
                          " WHEN BIG_VAR ='烧结煤' and FLAG = '下限' THEN 5 " \
                          " WHEN BIG_VAR ='烧结煤' and FLAG = '上限' THEN 6 " \
                          " ELSE 7 " \
                          " END " % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    big_var_tmp = row['big_var']
                    flag_tmp = row['参数名']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[(data_tmp['FLAG'] == flag_tmp) & (data_tmp['BIG_VAR'] == big_var_tmp)].index[0]
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['参数名'] == '性状比例上限' or row['参数名'] == '性状比例下限':
                    big_var_tmp = row['步骤']
                    if row['步骤'] == '喷吹煤':
                        sql = " select VAR,MIN_VALUE ,MAX_VALUE " \
                              " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                              " where TMPL_NO = '%s'  " \
                              " and FLAG = '性状' and BIG_VAR='喷吹煤' " \
                              " order by CASE VAR " \
                              " WHEN '烟煤' THEN 1 " \
                              " WHEN '无烟煤' THEN 2 " \
                              " WHEN '兰炭' THEN 3 " \
                              " ELSE 4 " \
                              " END " % (tmpl_no)
                    elif row['步骤'] == '发电煤':
                        sql = " select VAR,MIN_VALUE ,MAX_VALUE " \
                              " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                              " where TMPL_NO = '%s'  " \
                              " and FLAG = '性状' and BIG_VAR='发电煤' " \
                              " order by CASE VAR " \
                              " WHEN '大同类' THEN 1 " \
                              " WHEN '神府类' THEN 2 " \
                              " WHEN '兰炭类' THEN 3 " \
                              " ELSE 4 " \
                              " END " % (tmpl_no)
                    elif row['步骤'] == '烧结煤':
                        sql = " select VAR,MIN_VALUE ,MAX_VALUE " \
                              " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                              " where TMPL_NO = '%s'  " \
                              " and FLAG = '性状' and BIG_VAR='烧结煤' " \
                              " order by CASE VAR " \
                              " WHEN '无烟煤' THEN 1 " \
                              " WHEN '兰炭' THEN 2 " \
                              " WHEN '焦粉' THEN 3 " \
                              " ELSE 4 " \
                              " END " % (tmpl_no)
                    # sql = " select VAR,MIN_VALUE ,MAX_VALUE " \
                    #       " from BG00MAZZAI.T_ADS_WH_YLMX_INVLIMIT_INFO " \
                    #       " where TMPL_NO = '%s'  " \
                    #       " and FLAG = '性状' and BIG_VAR='%s' " \
                    #       " order by CASE VAR " \
                    #       " WHEN '烟煤' THEN 1 " \
                    #       " WHEN '无烟煤' THEN 2 " \
                    #       " WHEN '兰炭' THEN 3 " \
                    #       " ELSE 4 " \
                    #       " END " % (tmpl_no, big_var_tmp)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    var_tmp = row['VAR2']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['VAR'] == var_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['参数名'] == '品种比例上限' or row['参数名'] == '品种比例下限':
                    if row['步骤'] == '喷吹煤':
                        sql = sql1
                    elif row['步骤'] == '发电煤':
                        sql = sql2
                    elif row['步骤'] == '烧结煤':
                        sql = sql3
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    prod_dscr_tmp = row['PROD_DSCR']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['PROD_DSCR'] == prod_dscr_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index+1
                new_row = pd.Series(dict)
                df_out = df_out.append(new_row, ignore_index=True)
            result_list = df_out.to_dict(orient='records')
        # print(result_list)
        # print(message)
        # print('finish')
        # elapsed = float((datetime.datetime.now() - start).seconds)
        # print("Time Used 4 All ----->>>> %f seconds" % (elapsed))
        # print('finish')
        return message, result_list